/**  
 * Student Name: Thong, Wang Kit Wilson (50419704)
 *               Yin, Xing (51590277)
 */

#include "SqlDb.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "Log.h"
#include "SqlLog.h"

/**
 * Update the timestamp for the student in the StudentStatus table
 */
void 
UpdateStudentWriteSetId (sqlite3* db, int studentId)
{
	FUNC_LOG ();

	char *sql = sqlite3_mprintf ("insert or replace into studentStatus (studentId, isLocked) values (%d, 1);", studentId);
	SqlExec (db, sql);
	sqlite3_free (sql);
}

sqlite3* 
OpenDefaultDb ()
{
	FUNC_LOG ();

	sqlite3* db;
	if (sqlite3_open ("db.dat", &db)) {
		ERR_LOG (sqlite3_errmsg(db));
		sqlite3_close(db);
		exit(1);
	}	
	DEBUG_LOG ("Opened a session to the default DB");

	return db;
}

void 
CloseDefaultDb (sqlite3* db)
{
	FUNC_LOG ();

	sqlite3_close (db);
	INFO_LOG ("Closed the session to the default DB");
}

int 
SqlExec (sqlite3* db, char* sql)
{
	char **result;
	char* errMsg = 0;
	int numRows = 0;
	int numCols = 0;
	if (sqlite3_get_table (db, sql, &result, &numRows, &numCols, &errMsg) != SQLITE_OK) {
		ERR_LOG (errMsg);
		sqlite3_free (errMsg);
	}
	sqlite3_free_table (result);

	return numRows;
}

sqlite3_stmt* 
SqlPrepare (sqlite3* db, char* sql)
{
	sqlite3_stmt* ret_stmt = 0;
	if (sqlite3_prepare_v2 (db, sql, -1, &ret_stmt, NULL) != SQLITE_OK) {
		ERR_LOG (sqlite3_errmsg (db));
		exit (1);
	}

	return ret_stmt;
}

void 
InitDb (sqlite3* db)
{
	FUNC_LOG ();

	// create the 3 default tables
	SqlExec (db, "CREATE TABLE IF NOT EXISTS StudentStatus (latestWriteSetId default CURRENT_TIMESTAMP primary key, studentId INTEGER unique not null, studentName, isLocked BOOLEAN not null);");
	DEBUG_LOG ("StudentStatus table created");
	SqlExec (db, "CREATE TABLE IF NOT EXISTS BookTicketDetail (BookTicketNo INTEGER PRIMARY KEY ASC, studentId INTEGER not null, courtType INTEGER not null, startTime not null, numHours INTEGER not null);");	
	SqlExec (db, "CREATE INDEX IF NOT EXISTS Idx_BookTicketDetail_CourtType on BookTicketDetail (courtType);");
	DEBUG_LOG ("BookTicketDetail table created");
	SqlExec (db, "CREATE TABLE IF NOT EXISTS MessageLog (Id INTEGER PRIMARY KEY ASC, studentId INTEGER not null, commandType not null, argValue);");
	SqlExec (db, "CREATE INDEX IF NOT EXISTS Idx_MessageLog_commandType on MessageLog (commandType);");
	DEBUG_LOG ("MessageLog table created");

	DEBUG_LOG ("Three default tables created");
}

bool_t
IsSessionTimeout (sqlite3* db, int studentId)
{
	char** result;
	int rows, cols;
	char* errmsg;

	char* sql = sqlite3_mprintf ("select strftime('%%s', 'now') - strftime('%%s', latestWriteSetId) from StudentStatus where studentId = %d;", studentId);
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
	}
	sqlite3_free (sql);
	int inactiveTime = atoi (result[1]);
	sqlite3_free_table (result);

	return inactiveTime > 10*60;
}

/** 
 * Check if the student is currently locked
 */
bool_t 
IsLockedAlready (sqlite3* db, int studentId)
{
	char** result;
	int rows, cols;
	char* errmsg;
	bool_t ret;

	char* sql = sqlite3_mprintf ("select * from StudentStatus where studentId = %d and isLocked = 1", studentId);
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = TRUE;
	}
	sqlite3_free (sql);
	sqlite3_free_table (result);

	if (rows == 0) {	
		DEBUG_LOG ("The current student is not locked currently"); 		
		ret = FALSE;
	} else {		
		DEBUG_LOG ("The current student is being locked");
		ret = TRUE;
	}
	return ret;
}

/**
 * Perform the actual DbOpenSession logic after session lock is confirmed not locked
 */
ReturnType_t 
DoDbOpenSession (sqlite3* db, int studentId)
{
	ReturnType_t ret = 0;
	char** result;
	int rows, cols;
	char* errmsg = 0;
	char buf[100];
	
	char* sql = sqlite3_mprintf ("Select * from StudentStatus where studentId = %d;", studentId);
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	}
	sqlite3_free (sql);
	sqlite3_free_table (result);

	if (ret == 0) { // confirm not BAD_TECHNICAL_PROBLEM
		if (rows == 0) { // this student is new to this database 
			// mark this student as locked, and at the same time the timestamp is automatically generated
			sql = sqlite3_mprintf ("insert into StudentStatus (latestWriteSetId, studentId, isLocked) values (0, %d, 1);", studentId);
			if (sqlite3_exec (db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
				ERR_LOG (errmsg);
				sqlite3_free (errmsg);
				ret = BAD_TECHNICAL_PROBLEM;
			} else {
				ret = OK_BOOK_COURT_SYS;
				DEBUG_LOG ("return = OK_BOOK_COURT_SYS");
			}
			sqlite3_free (sql);
			DEBUG_LOG ("new student found - lock it for an exclusive use by the current session");
		} else if (rows > 0) {// this student exists in this database
			if (sqlite3_exec (db, "update StudentStatus set isLocked = 1", NULL, NULL, &errmsg) != SQLITE_OK) {
				ERR_LOG (errmsg);
				sqlite3_free (errmsg);
				ret = BAD_TECHNICAL_PROBLEM;
			} else {
				ret = OK_BOOK_COURT_SYS;
				DEBUG_LOG ("return = OK_BOOK_COURT_SYS");
			}
			DEBUG_LOG ("existing student - lock it for an exclusive use by the current session");
		} else {
			ERR_LOG ("Unexpected error!");
			exit (1);
		}
	}
	
	return ret;
}

ReturnType_t 
DbBegin (sqlite3* db)
{
	FUNC_LOG ();

	ReturnType_t ret = 0;
	char* errmsg;

	if (sqlite3_exec (db, "begin;", NULL, NULL, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	}

	if (ret == 0)
		ret = OK_BOOK_COURT_SYS;

	return ret;
}

ReturnType_t 
DbCommit (sqlite3* db)
{
	FUNC_LOG ();

	ReturnType_t ret = 0;
	char* errmsg;

	if (sqlite3_exec (db, "commit;", NULL, NULL, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	}

	if (ret == 0)
		ret = OK_BOOK_COURT_SYS;

	return ret;
}

ReturnType_t 
DbRollback (sqlite3* db)
{
	FUNC_LOG ();

	ReturnType_t ret = 0;
	char* errmsg;

	if (sqlite3_exec (db, "rollback;", NULL, NULL, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	}

	if (ret == 0)
		ret = OK_BOOK_COURT_SYS;

	return ret;
}

ReturnType_t 
DbOpenSession (sqlite3* db, int studentId)
{
	FUNC_LOG ();

	ReturnType_t ret = 0;
	char buf[100];

	if (IsLockedAlready (db, studentId)) { // leave immediately if the student is currently locked	
		// the TRUE keyword here allows session to be reopened always
		if (TRUE || IsSessionTimeout (db, studentId)) {
			if ((ret = DoDbOpenSession (db, studentId)) == OK_BOOK_COURT_SYS) {
				ret = WARN_SESSION_REOPENED;
			} // else, ret remains as it
		} else {
			ret = BAD_SESSION_ACCESS_DENIED;
			WARN_LOG ("return = BAD_SESSION_ACCESS_DENIED");
		}
	} else {
		ret = DoDbOpenSession (db, studentId);
	}
	return ret;
}

/**
 * Do the actual logic of DbCloseSession, after the session is confirmed to be exist
 */
ReturnType_t 
DoDbCloseSession (sqlite3* db, int studentId)
{
	ReturnType_t ret = 0;
	char* sql = 0;
	char* errmsg = 0;
	char buf[100];

	sql = sqlite3_mprintf ("update StudentStatus set isLocked = 0 where studentId = %d;", studentId);
	if (sqlite3_exec (db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (sql);
		ret = BAD_TECHNICAL_PROBLEM;
	} else {
		ret = OK_BOOK_COURT_SYS;
		DEBUG_LOG ("return = OK_BOOK_COURT_SYS");
	}
	sqlite3_free (sql);

	return ret;
}

ReturnType_t 
DbCloseSession (sqlite3* db, int studentId)
{
	FUNC_LOG ();

	ReturnType_t ret = 0;
	char* sql = 0;
	char* errmsg = 0;
	char** result;
	int rows, cols;
	char buf[100];

	sql = sqlite3_mprintf ("select * from StudentStatus where studentId = %d", studentId);
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret= BAD_TECHNICAL_PROBLEM;
	}
	sqlite3_free_table (result);
	sqlite3_free (sql);

	if (ret == 0) // make sure there is no error from the above SQL statement
	{
		if (rows == 1)
			ret = DoDbCloseSession (db, studentId);
		else if (rows == 0) {
			ret = BAD_SESSION_NOT_FOUND;
			WARN_LOG ("return = BAD_SESSION_NOT_FOUND");
		} else {
			ERR_LOG ("Unexpected error!");
			exit (1);
		}
	}

	return ret;
}

ReturnType_t 
DbReadStudentWriteSetId (sqlite3* db, int studentId, longlong_t* retWriteSetId)
{
	FUNC_LOG ();
	
	ReturnType_t ret = 0;
	char** result;
	int rows, cols;
	longlong_t writeSetId = 0;
	char* errmsg;
	char buf[200];

	char* sql = sqlite3_mprintf ("select strftime('%%s', latestWriteSetId) from StudentStatus order by latestWriteSetId desc;");
	sprintf (buf, "sql = %s", sql);
	DEBUG_LOG (buf);
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	}
	sqlite3_free (sql);	
	DEBUG_LOG ("after sqlite3_free()");
	if (rows == 0) {
		ret = BAD_WRITE_SET_ID_NOT_FOUND;
		WARN_LOG ("ret = BAD_WRITE_SET_ID_NOT_FOUND");
	}
	sprintf (buf, "number of rows returned = %d", rows);
	DEBUG_LOG (buf);
	if (ret == 0) {
		sprintf (buf, "selected from database the latest write set id = %s", result[1]);
		DEBUG_LOG (buf);
		writeSetId = atoll (result[1]);
		sprintf (buf, "new write set id = %lld", (longlong_t)writeSetId);
		DEBUG_LOG (buf);		
	}
	sqlite3_free_table (result);
	DEBUG_LOG ("after sqlite3_free_table()");
	
	if (ret == 0) {
		ret = OK_BOOK_COURT_SYS;
		*retWriteSetId = writeSetId;
	} else
		*retWriteSetId = 0;
	return ret;
}

ReturnType_t 
DbWriteStudentStatus (sqlite3* db, int studentId, longlong_t nextStudentWriteSetId)
{
	FUNC_LOG ();

	ReturnType_t ret = 0;
	char* errmsg;
	time_t writeSetId = 0;
	char buf[300];

	// assign a new write set id to the student in the StudentStatus table
	if (ret == 0) {
		char* sql;
		if (nextStudentWriteSetId <= 0)
			sql = sqlite3_mprintf ("insert or replace into studentStatus (studentId, isLocked) values (%d, 1);", studentId);	
		else
			sql = sqlite3_mprintf ("insert or replace into studentStatus (latestWriteSetId, studentId, isLocked) values (datetime ('%lld', 'unixepoch'), %d, 1);", nextStudentWriteSetId, studentId);	
		if (sqlite3_exec (db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
			ERR_LOG (errmsg);
			sqlite3_free (sql);
			ret = BAD_TECHNICAL_PROBLEM;
		}
		sqlite3_free (sql);
		DEBUG_LOG ("udpated the StudentStatus table");
	}

	if (ret == 0)
		ret = OK_BOOK_COURT_SYS;

	return ret;
}

/**
 * Return true if the current booking is a duplication
 */ 
bool_t 
BookingIsDuplicated (sqlite3* db, int studentId, CourtType_t courtType, time_t timeSlotStart, int numHour)
{
	FUNC_LOG ();

	char* sql = sqlite3_mprintf("select * from bookticketdetail where \
								studentid = %d and \
								courtType = %d and \
								strftime('%%s', starttime) = strftime('%%s', datetime ('%lld', 'unixepoch')) and \
								numhours = %d;", studentId, (int)courtType, (sqlite3_int64)timeSlotStart, numHour);
	DEBUG_LOG (sql);
	int numRows = SqlExec (db, sql);
	sqlite3_free (sql);

	char* debug = sqlite3_mprintf ("#rows duplicated = %d", numRows);
	DEBUG_LOG (debug);
	sqlite3_free (debug);

	return numRows != 0;
}

/**
 * Return ture if the current booking is in conflcit with existing bookings
 */
bool_t 
BookingConflicted (sqlite3* db, CourtType_t courtType, time_t timeSlotStart, int numHour)
{
	FUNC_LOG ();

	char* sql = sqlite3_mprintf("select * from bookticketdetail where \
								courtType = %d and \
								not (strftime('%%s', '%lld', 'unixepoch') > strftime('%%s', starttime, numHours || ' hours') or \
								strftime('%%s', '%lld', 'unixepoch', '%d hours') < strftime('%%s', starttime));", courtType, (sqlite3_int64)timeSlotStart, (sqlite3_int64)timeSlotStart, numHour);
	DEBUG_LOG (sql);
	int numRows = SqlExec (db, sql);
	sqlite3_free (sql);

	char* debug = sqlite3_mprintf ("#rows conflicted = %d", numRows);
	DEBUG_LOG (debug);
	sqlite3_free (debug);

	return numRows != 0;
}

/**
 * Do the actual logic after session lock is confirmed being locked
 */
ReturnType_t 
DoDbBookCourt (sqlite3* db, int studentId, CourtType_t courtType, time_t timeSlotStart, int numHour)
{
	ReturnType_t ret = 0;
	char* errmsg;

	if (BookingIsDuplicated (db, studentId, courtType, timeSlotStart, numHour)) {
		ret = BAD_DUPLICATED_BOOKING;
		WARN_LOG ("return = BAD_DUPLICATED_BOOKING");
	} else if (BookingConflicted (db, courtType, timeSlotStart, numHour)) {
		ret = BAD_TIME_SLOT_OCCUPIED;
		WARN_LOG ("return = BAD_TIME_SLOT_OCCUPIED");
	} 
	
	// place the booking
	if (ret == 0) {	
		char* sql = sqlite3_mprintf ("insert into BookTicketDetail (studentId, courtType, startTime, numHours) values (%d, %d, datetime ('%lld', 'unixepoch'), %d);", studentId, (int)courtType, (sqlite3_int64)timeSlotStart, numHour);
		if (sqlite3_exec (db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
			ERR_LOG (errmsg);
			sqlite3_free (errmsg);
			ret = BAD_TECHNICAL_PROBLEM;
		} 
		sqlite3_free (sql);
	}
	//// assign a new write set id to the student in the StudentStatus table
	//if (ret == 0) {
	//	char* sql = sqlite3_mprintf ("insert or replace into studentStatus (studentId, isLocked) values (%d, 1);", studentId);	
	//	if (sqlite3_exec (db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
	//		ERR_LOG (errmsg);
	//		sqlite3_free (sql);
	//		ret = BAD_TECHNICAL_PROBLEM;
	//	}
	//	sqlite3_free (sql);
	//}

	if (ret == 0) {
		ret = OK_BOOK_COURT_SYS;
		DEBUG_LOG ("return = OK_BOOK_COURT_SYS");
	}

	return ret;
}

/**
 * Get the new book ticket no just allocated
 */
DbBookCourtRet_t
GetNewBookTicketNo (sqlite3* db)
{
	FUNC_LOG ();

	char** result;
	int rows, cols;
	char* errmsg;
	ReturnType_t ret = 0;
	int bookTicketNo = 0;
	sqlite3_int64 newRow = sqlite3_last_insert_rowid (db);

	char* sql = sqlite3_mprintf ("select bookTicketNo from BookTicketDetail where ROWID = %lld", newRow);
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	}
	sqlite3_free (sql);

	if (ret == 0) {
		bookTicketNo = atoi (result[1]);
		sqlite3_free_table (result);
		ret = OK_BOOK_COURT_SYS;
		DEBUG_LOG ("return = OK_BOOK_COURT_SYS");
	}

	DbBookCourtRet_t dbRet;
	dbRet.returnType = ret;
	dbRet.bookTicketNo = bookTicketNo;
	return dbRet;
}

DbBookCourtRet_t 
DbBookCourt (sqlite3* db, int studentId, CourtType_t courtType, time_t timeSlotStart, int numHours)
{
	FUNC_LOG ();

	int bookTicketNo = 0;
	ReturnType_t ret = 0;
	char buf[100];

	if (!IsLockedAlready (db, studentId)) { // leave immediately if the student is currently locked		
		ret = BAD_SESSION_NOT_OPENED;
		WARN_LOG ("return = BAD_SESSION_NOT_OPENED");
	} else {
		ret = DoDbBookCourt (db, studentId, courtType, timeSlotStart, numHours);
	}

	if (ret == OK_BOOK_COURT_SYS) {
		DbBookCourtRet_t getTicketNoRet;
		getTicketNoRet = GetNewBookTicketNo (db);

		ret = getTicketNoRet.returnType;
		bookTicketNo = getTicketNoRet.bookTicketNo;

		char* debugmsg = sqlite3_mprintf ("New bookTicketNo = %d", bookTicketNo);
		DEBUG_LOG (debugmsg);
		sqlite3_free (debugmsg);
	}

	DbBookCourtRet_t dbRet;
	dbRet.returnType = ret;
	dbRet.bookTicketNo = bookTicketNo;
	return dbRet;
}

/**
 * Do the actual DbCancelCourt logic after confirmed that the session is being locked
 */
ReturnType_t 
DoDoDbCancelCourt (sqlite3* db, int studentId, int bookTicketNo) 
{
	char* errmsg = 0;
	ReturnType_t ret = 0;
	char buf[100];

	char* sql = sqlite3_mprintf ("delete from BookTicketDetail where studentId = %d and bookTicketNo = %d;", studentId, bookTicketNo);;
	if (sqlite3_exec (db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	} 
	sqlite3_free (sql);
	//// assign a new write set id to the student in the StudentStatus table
	//if (ret == 0) {
	//	sql = sqlite3_mprintf ("insert or replace into studentStatus (studentId, isLocked) values (%d, 1);", studentId);	
	//	if (sqlite3_exec (db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
	//		ERR_LOG (errmsg);
	//		sqlite3_free (sql);
	//		ret = BAD_TECHNICAL_PROBLEM;
	//	}
	//	sqlite3_free (sql);
	//}
	
	if (ret == 0) {
		ret = OK_BOOK_COURT_SYS;
		DEBUG_LOG ("return = OK_BOOK_COURT_SYS");
	}
	
	return ret;
}

/**
 * Check if there exists this BookTicketNo
 */
ReturnType_t 
DoDbCancelCourt (sqlite3* db, int studentId, int bookTicketNo) 
{
	char** result;
	int rows, cols;
	char* errmsg;
	ReturnType_t ret = 0;
	char buf[100];

	char* sql = sqlite3_mprintf ("select * from BookTicketDetail where studentId = %d and bookTicketNo = %d;", studentId, bookTicketNo);
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	}
	sqlite3_free_table (result);
	sqlite3_free (sql);

	if (ret == 0) {
		if (rows == 1) {
			ret = DoDoDbCancelCourt (db, studentId, bookTicketNo);
		} else if (rows == 0) {
			ret = BAD_TICKET_NO_NOT_FOUND;
			WARN_LOG ("retrun = BAD_TICKET_NO_NOT_FOUND");
		} else {
			ERR_LOG ("Unexpected error!");
			exit (1);
		}
	}
	return ret;
}

ReturnType_t 
DbCancelCourt (sqlite3* db, int studentId, int bookTicketNo)
{
	FUNC_LOG ();

	ReturnType_t ret = 0;
	char buf[100];

	if (!IsLockedAlready (db, studentId)) { // leave immediately if the student is currently locked		
		ret = BAD_SESSION_NOT_OPENED;
		WARN_LOG ("return = BAD_SESSION_NOT_OPENED");
	} else {
		ret = DoDbCancelCourt (db, studentId, bookTicketNo);
	}
	return ret;
}

/**
 * Do the actual logic of bookTicketNo retrival
 */
DbViewTimeSlotRet_t 
DoDbViewTimeSlot (sqlite3* db, CourtType_t courtType, time_t startTime, int numHours)
{
	char** result = 0;
	int rows, cols;
	char* errmsg = 0;
	ReturnType_t ret = 0;
	u_int numBookTicket = 0;
	int* vecBookTicketNo = 0;
	char buf[100];

	char* sql = sqlite3_mprintf("select bookTicketNo from BookTicketDetail where \
								courtType = %d and \
								not (strftime('%%s', %lld, 'unixepoch') > strftime('%%s', datetime(starttime, numHours || ' hours')) or \
								strftime('%%s', datetime(%lld, 'unixepoch', '%d hours')) < strftime('%%s', starttime)) \
								order by starttime;", courtType, (sqlite3_int64)startTime, (sqlite3_int64)startTime, numHours);	
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
		sqlite3_free (errmsg);
	}
	sqlite3_free (sql);

	if (ret == 0) {
		numBookTicket = (u_int) rows; // the row for the header is not counted
		vecBookTicketNo = (int*) malloc (numBookTicket*sizeof(int));
		// get all the book tickets within the specific time period
		int i;
		for (i=0; i<numBookTicket; i++) {
			vecBookTicketNo[i] = atoi (result[i+1]);
		}
		sqlite3_free_table (result);
		ret = OK_BOOK_COURT_SYS;
		DEBUG_LOG ("return = OK_BOOK_COURT_SYS");
	}

	DbViewTimeSlotRet_t dbRet;
	dbRet.returnType = ret;
	dbRet.vectorBookTicketNo.vectorBookTicketNo_len = numBookTicket;
	dbRet.vectorBookTicketNo.vectorBookTicketNo_val = vecBookTicketNo;
	return dbRet;
}

DbViewTimeSlotRet_t 
DbViewTimeSlot (sqlite3* db, int studentId, CourtType_t courtType, time_t startTime, int numHours)
{
	FUNC_LOG ();

	DbViewTimeSlotRet_t dbRet;
	char buf[100];

	if (!IsLockedAlready (db, studentId)) { // leave immediately if the student is currently locked		
		dbRet.returnType = BAD_SESSION_NOT_OPENED;
		WARN_LOG ("return = BAD_SESSION_NOT_OPENED");
	} else {
		dbRet = DoDbViewTimeSlot (db, courtType, startTime, numHours);
	}
	return dbRet;
}

/**
 * Do the actual logic of retrival detail of the bookTicketNo specified
 */
DbViewBookTicketNoRet_t 
DoDbViewBookTicketNo (sqlite3* db, int bookTicketNo)
{
	ReturnType_t ret = 0;
	char** result;
	int rows, cols;
	char* errmsg;
	char buf[100];
	DbViewBookTicketNoRet_t dbRet;

	char* sql = sqlite3_mprintf ("select s.studentId, ifnull(studentName, 'n/a'), courtType, strftime('%%s', startTime), numHours from bookticketdetail b, studentstatus s on (b.studentId = s.studentId) where bookticketno = %d;", bookTicketNo);
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	}
	sqlite3_free (sql);

	if (rows > 1) {
		sprintf (buf, "Unexpected error - rows = %d, cols = %d", rows, cols); 
		ERR_LOG (buf);
		exit (1);
	} 
	if (rows == 0) {
		ret = BAD_TICKET_NO_NOT_FOUND;
		WARN_LOG ("return = BAD_TICKET_NO_NOT_FOUND");
	} 	

	if (ret == 0) {
		dbRet.studentId = atoi (result[5]);
		dbRet.studentName = strdup (result[6]);
		dbRet.courtType = atoi (result[7]);
		dbRet.timeSlotStart = atoll (result[8]);
		dbRet.numHours = atoi (result[9]);
		dbRet.returnType = OK_BOOK_COURT_SYS;		
		DEBUG_LOG ("return = OK_BOOK_COURT_SYS");

		sqlite3_free_table (result);
	} else {
		dbRet.studentId = 0;
		dbRet.studentName = 0;
		dbRet.courtType = -1;
		dbRet.timeSlotStart = 0;
		dbRet.numHours = 0;		
		dbRet.returnType = ret;	
	}

	return dbRet;
}

DbViewBookTicketNoRet_t 
DbViewBookTicketNo (sqlite3* db, int studentId, int bookTicketNo)
{
	FUNC_LOG ();

	DbViewBookTicketNoRet_t dbRet;
	char buf[100];

	if (!IsLockedAlready (db, studentId)) { // leave immediately if the student is currently locked		
		dbRet.returnType = BAD_SESSION_NOT_OPENED;
		WARN_LOG ("return = BAD_SESSION_NOT_OPENED");
	} else {
		dbRet = DoDbViewBookTicketNo (db, bookTicketNo);
	}
	return dbRet;
}

/**
 * Do the actual logic of retrieving bookTicketNo
 */
DbViewStudentRet_t 
DoDbViewStudent (sqlite3* db, int studentId)
{
	ReturnType_t ret = 0;
	char** result = 0;
	int rows, cols;
	char* errmsg = 0;
	int numBookTicket = 0;
	int* vecBookTicketNo = 0;

	char* sql = sqlite3_mprintf ("select bookTicketNo from BookTicketDetail where studentId = %d", studentId);
	if (sqlite3_get_table (db, sql, &result, &rows, &cols, &errmsg) != SQLITE_OK) {
		ERR_LOG (errmsg);
		sqlite3_free (errmsg);
		ret = BAD_TECHNICAL_PROBLEM;
	}
	sqlite3_free (sql);

	if (ret == 0) {
		numBookTicket = (u_int) rows; // the row for the header is not counted
		vecBookTicketNo = (int*) malloc (numBookTicket*sizeof(int));
		// get all the book tickets within the specific time period
		int i;
		for (i=0; i<numBookTicket; i++) {
			vecBookTicketNo[i] = atoi (result[i+1]);
		}
		sqlite3_free_table (result);
		ret = OK_BOOK_COURT_SYS;
		DEBUG_LOG ("return = OK_BOOK_COURT_SYS");
	}

	DbViewStudentRet_t dbRet;
	dbRet.returnType = ret;
	dbRet.vectorBookTicketNo.vectorBookTicketNo_len = numBookTicket;
	dbRet.vectorBookTicketNo.vectorBookTicketNo_val = vecBookTicketNo;
	return dbRet;
}

DbViewStudentRet_t 
DbViewStudent (sqlite3* db, int studentId)
{
	FUNC_LOG ();

	DbViewStudentRet_t dbRet;
	char buf[100];

	if (!IsLockedAlready (db, studentId)) { // leave immediately if the student is currently locked		
		dbRet.returnType = BAD_SESSION_NOT_OPENED;
		WARN_LOG ("return = BAD_SESSION_NOT_OPENED");
	} else {
		dbRet = DoDbViewStudent (db, studentId);
	}
	return dbRet;
}

const char* 
GetCourtTypeString (CourtType_t court)
{
	const char* str;
	switch (court) {
	case Badminton:		str = "Badminton"; break;
	case TableTennis:	str = "TableTennis"; break;
	default:			str = "<unknown>"; break;
	}
	return str;
}

const char* 
GetReturnTypeString (ReturnType_t ret)
{
	const char* str;
	switch (ret) {
	case OK_BOOK_COURT_SYS:				str = "OK_BOOK_COURT_SYS"; break;
	case BAD_SERVER_BUSY:				str = "BAD_SERVER_BUSY"; break;
	case BAD_TIME_SLOT_OCCUPIED:		str = "BAD_TIME_SLOT_OCCUPIED"; break;
	case BAD_DUPLICATED_BOOKING:		str = "BAD_DUPLICATED_BOOKING"; break;
	case BAD_TECHNICAL_PROBLEM:			str = "BAD_TECHNICAL_PROBLEM"; break;
	case BAD_UNEXPECTED_WRITE_SET_ID:		str = "BAD_UNEXPECTED_WRITE_SET_ID"; break;
	case BAD_SESSION_NOT_OPENED:		str = "BAD_SESSION_NOT_OPENED"; break;
	case BAD_SESSION_ACCESS_DENIED:		str = "BAD_SESSION_ACCESS_DENIED"; break;
	case BAD_SESSION_NOT_FOUND:			str = "BAD_SESSION_NOT_FOUND"; break;
	case WARN_SESSION_REOPENED:		str = "WARN_SESSION_REOPENED"; break;
	case BAD_TICKET_NO_NOT_FOUND:		str = "BAD_TICKET_NO_NOT_FOUND"; break;
	case BAD_MESSAGE_LOG_NOT_FOUND:		str = "BAD_MESSAGE_LOG_NOT_FOUND"; break;
	case BAD_WRITE_SET_ID_NOT_FOUND:	str = "BAD_WRITE_SET_ID_NOT_FOUND"; break;
	default:							str = "<unknown>"; break;
	};
	return str;
}

void 
TestDb ()
{
	FUNC_LOG ();
	
	srand (time(NULL));

	char buf[500] = "";

	sqlite3* db = OpenDefaultDb ();
	InitDb (db);

	struct tm tmTimeSlotStart;
	time_t timeSlotStart;

	tmTimeSlotStart.tm_year = 2010-1900;	
	tmTimeSlotStart.tm_mon = 3-1;
	tmTimeSlotStart.tm_mday = 10;
	tmTimeSlotStart.tm_hour = rand () % 24;
	tmTimeSlotStart.tm_min = 0;
	tmTimeSlotStart.tm_sec = 0;
	timeSlotStart = mktime (&tmTimeSlotStart);	

	DbOpenSession (db, 50419704);
	DbOpenSession (db, 50419704);
	longlong_t writeSetId;
	DbWriteStudentStatus (db, 50419704, 0);
	DbBookCourt (db, 50419704, Badminton, timeSlotStart, 2);
	DbReadStudentWriteSetId (db, 50419704, &writeSetId);
	DbBookCourt (db, 50419704, Badminton, timeSlotStart, 3);
	DbBookCourt (db, 50419704, Badminton, timeSlotStart, 4);
	DbBookCourt (db, 50419704, TableTennis, timeSlotStart, 5);
	DbCancelCourt (db, 50419704, 1);
	DbCancelCourt (db, 50419704, 1);
	DbBookCourt (db, 51514647, Badminton, timeSlotStart, 3);

	DbReadStudentWriteSetId (db, 50419704, &writeSetId);
	DbReadStudentWriteSetId (db, 123, &writeSetId);

	DbWriteStudentStatus (db, 5555, 798456);
	DbWriteStudentStatus (db, 50419704, -3);

	DbViewTimeSlotRet_t allTickets = DbViewTimeSlot (db, 50419704, Badminton, timeSlotStart, 10);
	if (allTickets.returnType == OK_BOOK_COURT_SYS) {
		int i;
		for (i=0; i<allTickets.vectorBookTicketNo.vectorBookTicketNo_len; i++) {
			int bookTicketNo = allTickets.vectorBookTicketNo.vectorBookTicketNo_val[i];
			fprintf (stdout, "%d: book ticket no = %d\n", i+1, bookTicketNo);

			DbViewBookTicketNoRet_t ticketDetail = DbViewBookTicketNo (db, 50419704, bookTicketNo);	
			if (ticketDetail.returnType == OK_BOOK_COURT_SYS) {
				fprintf (stdout, "    student ID   = %d\n", ticketDetail.studentId);
				fprintf (stdout, "    student name = %s\n", ticketDetail.studentName);
				fprintf (stdout, "    start time   = %s", ctime (&ticketDetail.timeSlotStart));
				fprintf (stdout, "    duration     = %d hours\n", ticketDetail.numHours);
				switch (ticketDetail.courtType) {
					case Badminton:
						fprintf (stdout, "    court        = Badminton\n"); break;
					case TableTennis:
						fprintf (stdout, "    court        = TableTennis\n"); break;
					default:
						fprintf (stdout, "    court        = Unknown\n"); break;
				};
				free (ticketDetail.studentName);
			}
		}
		free (allTickets.vectorBookTicketNo.vectorBookTicketNo_val);
	}

	DbViewStudentRet_t studentTickets = DbViewStudent (db, 50419704);
	if (studentTickets.returnType == OK_BOOK_COURT_SYS) {
		int len = studentTickets.vectorBookTicketNo.vectorBookTicketNo_len;
		int* val = studentTickets.vectorBookTicketNo.vectorBookTicketNo_val;

		int i;
		for (i=0; i<len; i++) {
			fprintf (stdout, "%d: book ticket no = %d\n", i+1, val[i]);
		}
	}

	BookCourtArgs_t bookArgsToLog;
	bookArgsToLog.courtType = Badminton;
	bookArgsToLog.lastStudentWriteSetId = 10;
	bookArgsToLog.numHours = 3;
	bookArgsToLog.studentId = 50419704;
	bookArgsToLog.studentName = 0;
	bookArgsToLog.timeSlotStart.tm_year = 2010;
	bookArgsToLog.timeSlotStart.tm_mday = 14;
	bookArgsToLog.timeSlotStart.tm_mon = 30;
	bookArgsToLog.timeSlotStart.tm_hour = 10;
	LogEnqueueMessage (db, 50419704, BookCourtComm, &bookArgsToLog, sizeof (BookCourtArgs_t));

	void* argsFromLog = 0;
	BookCourtArgs_t* bookArgsFromLog = 0;
	WriteCommandType_t commType;
	LogReadHeadMessage (db, 50419704, &commType, &argsFromLog);
	DEBUG_LOG ("LogReadHeadMessage() returned");
	sprintf (buf, "argsFromLog pointer value = %p", argsFromLog);
	DEBUG_LOG (buf);
	sprintf (buf, "commType = %d", commType);
	DEBUG_LOG (buf);
	switch (commType) {
		case BookCourtComm:
			bookArgsFromLog = (BookCourtArgs_t*)argsFromLog; break;
		case CancelCourtComm:
			ERR_LOG ("It is NOT a cancel command!"); exit(1);
		default: 
			ERR_LOG ("Unknown command type"); exit(1);
	};
	fprintf (stdout, "\tstudent ID = %d\n", bookArgsFromLog->studentId);
	fprintf (stdout, "\tcourt type = %d\n", bookArgsFromLog->courtType);
	fprintf (stdout, "\tstart time = %d-%d-%d %d:00\n", 
		bookArgsFromLog->timeSlotStart.tm_year, 
		bookArgsFromLog->timeSlotStart.tm_mon,
		bookArgsFromLog->timeSlotStart.tm_mday,
		bookArgsFromLog->timeSlotStart.tm_hour);
	fprintf (stdout, "\tlast write set ID = %d\n", bookArgsFromLog->lastStudentWriteSetId);
	fprintf (stdout, "\tbook duration= %d hours\n", bookArgsFromLog->numHours);	
	free (argsFromLog);

	LogDequeueMessage (db, 50419704);
	LogDequeueMessage (db, 50419704);
	LogDequeueMessage (db, 50419704);

	DbCloseSession (db, 50419704);
	DbCloseSession (db, 10101010);

	CloseDefaultDb (db);

	INFO_LOG ("Tested sqlite3 code");
	exit(1);
}
